package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.data.repositories.WeekReprotRepository;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created by HangLiu on 2016/11/16.
 */
@Repository
public class WeekReprotRepositoryImpl extends BaseRepository implements WeekReprotRepository {
    /***************  获取车的编号 ***************/
    public List getCarType(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer(" " +
                "   SELECT  " +
                "   cx.`车型编号` AS id,   " +
                "  IFNULL(cx.`车型_DE`,' ') AS `Baumuster` ,  " +
                "  IFNULL( cx.`分类`, ' ') AS  `Segments`, " +
                "  IFNULL(cx.`底盘`,  ' ') AS `Chassis`, " +
                "  IFNULL( cx.`车型` , ' ') AS `Model Short` " +
                "  FROM   " +
                " `lsh车型` AS cx  " +
                "  JOIN  `lsh库存表` AS kc ON cx.`车型编号`=kc.`车型编号`" +
                "  WHERE kc.`所属经销商`=:permit" +
                "  GROUP BY cx.`车型编号` ");
        return this.getList(sql.toString(), params);
    }

    /************** 查询订单***************///更改日期11/24

    //本周订单
    @Override
    public List getWeekOrders(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT COUNT(DISTINCT   bjd.`订单号` ) 周订单数 " +
                "  FROM  `lsh车型`  AS cx " +
                "  LEFT  JOIN `报价单表`  AS  bj ON  cx.`车型编号`=bj.`车型编号`  " +
                "  LEFT   JOIN  `报价订单表` AS  bjd ON  bj.`订单编号`=bjd.`订单编号`   " +
                " WHERE  bj.`许可`=:permit  " +
                "   AND  cx.`车型编号`=:carTypeId  " +
                "   AND  bjd.`订单状态`<> '退订'   " +
                "  AND bjd.`订单号` IS NOT NULL " +
                "    AND  WEEKOFYEAR(bjd.`订单签署日期`)=WEEKOFYEAR(NOW()) " +
                "   AND  YEAR(bjd.`订单签署日期`)=YEAR(NOW()) ");
        return this.getList(sql.toString(), params);
    }

    //本月订单
    @Override
    public List getMonthOrders(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT COUNT(DISTINCT   bjd.`订单号` ) 月订单数 " +
                "  FROM  `lsh车型`  AS cx " +
                "  LEFT  JOIN `报价单表`  AS  bj ON  cx.`车型编号`=bj.`车型编号`  " +
                "  LEFT   JOIN  `报价订单表` AS  bjd ON  bj.`订单编号`=bjd.`订单编号`   " +
                " WHERE  bj.`许可`=:permit  " +
                "   AND  cx.`车型编号`=:carTypeId  " +
                "   AND  bjd.`订单状态`<> '退订'   " +
                "  AND bjd.`订单号` IS NOT NULL " +
                "   AND  MONTH(bjd.`订单签署日期`)= MONTH(NOW())" +
                "   AND  YEAR(bjd.`订单签署日期`)=YEAR(NOW()) ");
        return this.getList(sql.toString(), params);
    }

    //本年订单
    @Override
    public List getYearOrders(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT  COUNT(DISTINCT   bjd.`订单号` ) 年订单数 " +
                "  FROM  `lsh车型`  AS cx " +
                "  LEFT  JOIN `报价单表`  AS  bj ON  cx.`车型编号`=bj.`车型编号`  " +
                "  LEFT   JOIN  `报价订单表` AS  bjd ON  bj.`订单编号`=bjd.`订单编号`   " +
                " WHERE  bj.`许可`=:permit  " +
                "   AND  cx.`车型编号`=:carTypeId  " +
                "   AND  bjd.`订单状态`<> '退订'   " +
                "  AND bjd.`订单号` IS NOT NULL " +
                "   AND  YEAR(bjd.`订单签署日期`)=YEAR(NOW()) ");
        return this.getList(sql.toString(), params);
    }

    //尚未开票订单
    //更改日期11/24
    @Override
    public List getNoInvOrders(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT COUNT(1) AS  未开票订单 " +
                " FROM  `lsh车型`  AS cx    " +
                "   JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号` " +
                "   JOIN `报价单表` AS bj ON bj.`报价编号`=kc.`报价编号`" +
                "   JOIN  ( SELECT  *,MAX(aa.`报价编号`) AS `单号`  FROM  `报价单表` AS aa  group by `初始报价单号` )AS x ON x.`初始报价单号`= bj.`初始报价单号` " +
                "   JOIN  `报价交车表` AS jc ON bj.`交车`=jc.`编号`   " +
                "  WHERE  bj.`许可`=:permit " +
                "   AND  bj.`报价编号`=x.`单号`" +
                "   AND  kc.`车架号` NOT LIKE 'IRISTechnologies%' " +
                "  AND  cx.`车型编号`=:carTypeId  " +
                "   AND jc.`开票提交时间` IS NULL  ");

        return this.getList(sql.toString(), params);
    }

    /***********查询零售********************/

    //本周零售
    @Override
    public List getWeekSales(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " select count(1) as '周零售数', a.许可  " +
                " from vw_retailed_quotation a   " +
                " join LSH车型 e on a.车型编号 = e.车型编号 " +
                " where a.`许可`=:permit " +
                " AND  a.`车型编号`=:carTypeId " +
                " AND WEEKOFYEAR(a.`交车日期`)=WEEKOFYEAR(NOW()) " +
                " AND YEAR(a.`交车日期`)=YEAR(NOW()) ");

        return this.getList(sql.toString(), params);
    }

    //本月零售
    @Override
    public List getMonthSales(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " select count(1) as '月零售数', a.许可  " +
                " from vw_retailed_quotation a   " +
                " join LSH车型 e on a.车型编号 = e.车型编号 " +
                " where a.`许可`=:permit " +
                " AND  a.`车型编号`=:carTypeId " +
                " AND MONTH(a.`交车日期`)=MONTH(NOW()) " +
                " AND YEAR(a.`交车日期`)=YEAR(NOW())  ");

        return this.getList(sql.toString(), params);
    }

    //本年度零售
    @Override
    public List getYearSales(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " select count(1) as '年零售数', a.许可  " +
                " from vw_retailed_quotation a  " +
                " join LSH车型 e on a.车型编号 = e.车型编号 " +
                " where a.`许可`=:permit " +
                " AND  a.`车型编号`=:carTypeId " +
                " AND YEAR(a.`交车日期`)=YEAR(NOW()) ");

        return this.getList(sql.toString(), params);
    }

    /****************查询库存*************/
    //有订单的库存
    @Override
    public List getOrderStock(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT  COUNT(1) AS 有单库存" +
                "  FROM  `lsh车型`  AS cx    " +
                "  JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号` " +
                "  JOIN `报价单表` AS bj ON bj.`报价编号`=kc.`报价编号`  " +
                "  JOIN  ( SELECT  *,MAX(aa.`报价编号`) AS `单号`  FROM  `报价单表` AS aa  group by `初始报价单号` )AS x ON x.`初始报价单号`= bj.`初始报价单号`" +
                "  JOIN  `报价交车表` AS jc ON bj.`交车`=jc.`编号`   " +
                "  WHERE  bj.`许可`=:permit " +
                "  AND  bj.`报价编号`=x.`单号` " +
                "  AND kc.`车架号` NOT LIKE 'IRISTechnologies%' " +
                "  AND  cx.`车型编号`=:carTypeId  " +
                "  AND  jc.`交车日期` IS NULL ");
        return this.getList(sql.toString(), params);
    }

    //无订单的库存
    @Override
    public List getNoOrderStock(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT COUNT(1) AS 无单库存 " +
                "  FROM  `lsh车型`  AS cx    " +
                "  LEFT JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号` " +
                "  WHERE kc.`所属经销商` =:permit " +
                "  AND (kc.`调车物流状态` IN ( '调出申请确认' , '调出出库' , '调入入库' ) OR ISNULL( kc.`调车物流状态` ) ) " +
                "  AND kc.`车辆零售状态`<> '已卖' " +
                "  AND kc.`车架号` NOT LIKE 'IRISTechnologies%'  " +
                "  AND  cx.`车型编号`=:carTypeId ");
        return this.getList(sql.toString(), params);
    }

    //已经交车但尚未开票库存
    @Override
    public List getNoInvStock(String permit, String carTypeId) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT  COUNT(1) AS  交车未开票 " +
                "  FROM  `lsh车型`  AS cx    " +
                "   JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号` " +
                "   JOIN `报价单表` AS bj ON bj.`报价编号`=kc.`报价编号` " +
                " JOIN  ( SELECT  *,MAX(aa.`报价编号`) AS `单号`  FROM  `报价单表` AS aa  group by `初始报价单号` )AS x ON x.`初始报价单号`= bj.`初始报价单号` " +
                "   JOIN  `报价交车表` AS jc ON bj.`交车`=jc.`编号`   " +
                "  WHERE  bj.`许可`=:permit" +
                " AND  bj.`报价编号`=x.`单号` " +
                "  AND  kc.`车架号` NOT LIKE 'IRISTechnologies%' " +
                "  AND  cx.`车型编号`=:carTypeId " +
                "  AND  jc.`交车日期` IS  NOT NULL   AND jc.`开票提交时间` IS NULL  ");
        return this.getList(sql.toString(), params);
    }

    /********对应月份生产且未出售的资源********/
    @Override
    public List getNoSaleCars(String permit, String carTypeId, String year, String month) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("carTypeId", carTypeId);
        params.put("year", year);
        params.put("month", month);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT  COUNT(1) AS "
                + year
                + month
                + "生产未卖 " +
                "  FROM  `lsh车型`  AS cx    " +
                "  LEFT JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号` " +
                " WHERE  kc.`所属经销商` =:permit " +
                "   AND kc.`车架号` NOT LIKE 'IRISTechnologies%'  " +
                "   AND kc.`车辆零售状态`='未卖' " +
                "   AND  cx.`车型编号`=:carTypeId " +
                "   AND  YEAR( kc.`生产日期`)=:year AND MONTH(kc.`生产日期`)=:month  ");
        return this.getList(sql.toString(), params);
    }

    @Override
    //区域集团报表对应月份生产但尚未被采购的资源
    public List getNoSaleCars(String permit, String date) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("date", date);
        StringBuffer sql = new StringBuffer(" SELECT  uniqueCode,分类,车型_DE,底盘,车型,CONCAT(Jan,'|',Feb,'|',Mar,'|',Apr,'|',May,'|',Jun,'|',Jul,'|',Aug,'|',Sep,'|',Oct,'|',Nov,'|',Deco) as months FROM(\n" +
                "SELECT  kc.`所属经销商` as 许可,CONCAT(cx.`分类`,'_',cx.`车型_DE`,'_',cx.`底盘`,'_',cx.`车型`) as uniqueCode,cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`,\n" +
                "sum(if( MONTH(kc.`生产日期`) =1, 1, 0)) Jan,\n" +
                "sum(if( MONTH(kc.`生产日期`) =2, 1, 0))  Feb,\n" +
                "sum(if( MONTH(kc.`生产日期`) =3, 1, 0)) Mar,\n" +
                "sum(if( MONTH(kc.`生产日期`) =4, 1, 0)) Apr,\n" +
                "sum(if( MONTH(kc.`生产日期`) =5, 1, 0)) May,\n" +
                "sum(if( MONTH(kc.`生产日期`) =6, 1, 0)) Jun,\n" +
                "sum(if( MONTH(kc.`生产日期`) =7, 1, 0)) Jul,\n" +
                "sum(if( MONTH(kc.`生产日期`) =8, 1, 0)) Aug,\n" +
                "sum(if( MONTH(kc.`生产日期`) =9, 1, 0)) Sep,\n" +
                "sum(if( MONTH(kc.`生产日期`) =10, 1, 0)) Oct ,\n" +
                "sum(if( MONTH(kc.`生产日期`) =11, 1, 0)) Nov,\n" +
                "sum(if( MONTH(kc.`生产日期`) =12, 1, 0)) Deco\n" +
                "\t\tFROM  `lsh车型`  AS cx   \n" +
                "LEFT JOIN `lsh库存表` AS kc ON ( kc.`车型编号` = cx.`车型编号` " +
                "AND kc.`所属经销商` =:permit AND kc.`车架号` NOT LIKE 'IRISTechnologies%' " +
                "AND kc.`车辆零售状态` = '未卖' " +
                " and kc.`批发日期` is NULL  and   kc.is_delete =0 " +
                "AND YEAR (kc.`生产日期`) =YEAR(:date ) )\n" +
                " WHERE  cx.`车型编号`<>0  AND cx.分类 IS NOT NULL " +
                "\tGROUP BY cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`) aa;");
        return this.getList(sql.toString(), params);
    }

    @Override
//##区域集团报表获取订单数 周订单 月订单
    public List getOrderCars(String permit, String date) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("date", date);
        StringBuffer sql = new StringBuffer(" " +
                " select b.许可,CONCAT(d.`分类`,'_',d.`车型_DE`,'_',d.`底盘`,'_',d.`车型`) as uniqueCode,d.`分类`,d.`车型_DE`,d.`底盘`,d.`车型`,   \n" +
                " sum( if(WEEKOFYEAR(订单签署日期)  = WEEKOFYEAR(:date), 1, 0)) weekly_customer_order,\n" +
                " sum(if(MONTH(订单签署日期)  = MONTH(:date), 1, 0)) monthyly_customer_order,\n" +
                " sum(if(YEAR(订单签署日期)  = YEAR(:date), 1, 0))  yearly_customer_order\n" +
                " from (     \n" +
                " select 报价单表.* from 报价单表 报价单表,     \n" +
                " (select max(b.报价编号) 报价编号 from lsh库存表 a join 报价单表 b on a.报价编号 = b.报价编号     \n" +
                " where b.是否驳回 = '否' and a.车辆零售状态 <> '未卖'  group by b.初始报价单号) MAXNO     \n" +
                " where 报价单表.报价编号 = MAXNO.报价编号     \n" +
                " ) b      \n" +
                " left join 报价订单表 c on b.订单编号 = c.订单编号     \n" +
                " left join LSH车型 d on b.`车型编号` = d.车型编号     \n" +
                " where c.订金类型 = '订金'        \n" +
                " and c.订单审批通过日期 is not null       \n" +
                " and c.订单失销日期 is null       \n" +
                " and  YEAR(c.订单审批通过日期) >= YEAR(:date)         \n" +
                " and b.许可=:permit  " +
                " and d.`车型编号`<>0  AND d.分类 IS NOT NULL \n" +
                " group by  d.`分类`,d.`车型_DE`,d.`底盘`,d.`车型`  \n");

        return this.getList(sql.toString(), params);
    }

    @Override
//##区域集团报表获取零售数
    public List getSalesCars(String permit, String date) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("date", date);
        StringBuffer sql = new StringBuffer(" " +
                " SELECT a.许可,CONCAT(cx.`分类`,'_',cx.`车型_DE`,'_',cx.`底盘`,'_',cx.`车型`) as uniqueCode, cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`,\n" +
                " sum(if(WEEKOFYEAR(开票时间)  = WEEKOFYEAR(:date),1,0)) RETAIL_SALE_WEEKLY,\n" +
                " sum(if(MONTH(开票时间)  = MONTH(:date),1,0)) RETAIL_SALE_MTD,\n" +
                " sum(if(YEAR(开票时间)  = YEAR(:date),1,0))  RETAIL_SALE_YTD\n" +
                " FROM\n" +
                " `lsh车型` AS cx \n" +
                " LEFT  JOIN vw_retailed_quotation AS a ON cx.车型编号=a.`车型编号`\n" +
                " WHERE  a.`许可`=:permit\n" +
                " AND YEAR(开票时间)  = YEAR(:date)\n" +
                " AND a.`是否驳回`='否'\n" +
                " AND a.`车辆零售状态`\t='已卖'\n" +
                " AND a.`退票时间` is NULL\n" +
                " AND a.退车日期 is null  " +
                " AND cx.`车型编号`<>0  AND cx.分类 IS NOT NULL          \n" +
                " GROUP BY  cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`;\n");
        return this.getList(sql.toString(), params);
    }

    @Override
//##未开票订单
    public List getNoInvOrders(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer(" " +
                " select b.`许可`,CONCAT(d.`分类`,'_',d.`车型_DE`,'_',d.`底盘`,'_',d.`车型`) as uniqueCode,d.`分类`,d.`车型_DE`,d.`底盘`,d.`车型`, \t\n" +
                " COUNT(1) AS  CUSTOMER_ORDER_TOTAL_UNFULFILLED\n" +
                " from vw_retailed_quotation b, 报价订单表 c, LSH车型 d      \n" +
                " where b.`许可`=:permit \n" +
                " and b.订单编号 = c.订单编号     \n" +
                " and b.车辆零售状态 = '已卖'   \n" +
                " and c.订金类型 = '订金'      \n" +
                " and c.订单审批通过日期 is not null     \n" +
                " and c.订单失销日期 is null  \n" +
                " and `b`.`车型编号` = `d`.`车型编号` \n" +
                " and `b`.`退车日期` is null      \n" +
                " AND `b`.`开票时间` IS NULL\n" +
                " AND d.`车型编号`<>0  AND d.分类 IS NOT NULL \n" +
                " GROUP BY d.`分类`,d.`车型_DE`,d.`底盘`,d.`车型`;\n");
        return this.getList(sql.toString(), params);
    }

    /*有单库存*/
    @Override
    public List getStockWithOrder(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer(" SELECT   bj.许可,CONCAT(cx.`分类`,'_',cx.`车型_DE`,'_',cx.`底盘`,'_',cx.`车型`) as uniqueCode, cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型` ,\tCOUNT(1) AS STOCK_WITH_ORDER\n" +
                "FROM  `lsh车型`  AS cx   \n" +
                "JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号`\t\n" +
                "JOIN `报价单表` AS bj ON bj.`报价编号`=kc.`报价编号`\n" +
                "JOIN  ( SELECT  *,MAX(aa.`报价编号`) AS `单号`  FROM  `报价单表` AS aa  group by `初始报价单号` )AS x ON x.`初始报价单号`= bj.`初始报价单号`\n" +
                "JOIN  `报价交车表` AS jc ON bj.`交车`=jc.`编号`  \n" +
                "WHERE  bj.`许可`=:permit \n" +
                "AND  bj.`报价编号`=x.`单号`\n" +
                "AND kc.`车架号` NOT LIKE 'IRISTechnologies%'\n" +
                "AND  jc.`开票时间` IS NULL " +
                " AND cx.`车型编号`<>0  AND cx.分类 IS NOT NULL \n" +
                "GROUP BY cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`;\n");
        return this.getList(sql.toString(), params);
    }

    /*无单库存*/
    @Override
    public List getStockWithOutOrder(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer("" +
                " SELECT kc.所属经销商 AS 许可 ,CONCAT(cx.`分类`,'_',cx.`车型_DE`,'_',cx.`底盘`,'_',cx.`车型`) as uniqueCode,cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型` , count(1) AS STOCK_WITHOUT_ORDER\n" +
                " FROM  `lsh车型`  AS cx   \n" +
                "  JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号`\n" +
                " WHERE kc.`所属经销商` =:permit\n" +
                " AND (kc.`调车物流状态` IN ('调出申请确认','调出出库','调入入库') OR ISNULL(kc.`调车物流状态`))\n" +
                " AND (kc.`车辆零售状态`<>'已卖' and kc.`车辆零售状态`<>'审批中')\n" +
                " and kc.`出库日期` is NULL\n" +
                " and kc.`批发日期` is not NULL\n" +
                " and kc.`批发日期`<CURDATE()\n" +
                " and kc.报价编号 is null\n" +
                " AND cx.`车型编号`<>0  AND cx.分类 IS NOT NULL\n" +
                " AND kc.`车架号` NOT LIKE 'IRISTechnologies%'\t\n" +
                " GROUP BY cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`;\n");
        return this.getList(sql.toString(), params);
    }

    /*交车但尚未开票的车*/
    @Override
    public List getNoInvStock(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer(" SELECT \tbj.`许可` ,CONCAT(cx.`分类`,'_',cx.`车型_DE`,'_',cx.`底盘`,'_',cx.`车型`) as uniqueCode,cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型` ,COUNT(1) AS  STOCK_PENDING_FOR_INVOICE\n" +
                " FROM  `lsh车型`  AS cx   \n" +
                " JOIN `lsh库存表` AS kc ON kc.`车型编号`=cx.`车型编号`\n" +
                " JOIN `报价单表` AS bj ON bj.`报价编号`=kc.`报价编号`\n" +
                " JOIN  ( SELECT  *,MAX(aa.`报价编号`) AS `单号`  FROM  `报价单表` AS aa  group by `初始报价单号` )AS x ON x.`初始报价单号`= bj.`初始报价单号`\n" +
                " JOIN  `报价交车表` AS jc ON bj.`交车`=jc.`编号`  \n" +
                " WHERE  bj.`许可`=:permit \n" +
                " AND  bj.`报价编号`=x.`单号`\n" +
                " AND  kc.`车架号` NOT LIKE 'IRISTechnologies%'\t\t\n" +
                " AND  jc.`交车日期` IS  NOT NULL   AND jc.`开票提交时间` IS NULL\n " +
                " AND cx.`车型编号`<>0  AND cx.分类 IS NOT NULL " +
                " GROUP BY cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`;\n");
        return this.getList(sql.toString(), params);
    }

    /*开票但尚未交车的车*/
    @Override
    public List getInvoiceNoGive(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer("" +
                " SELECT a.许可,CONCAT(cx.`分类`,'_',cx.`车型_DE`,'_',cx.`底盘`,'_',cx.`车型`) as uniqueCode, cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`,\n" +
                " count(1) as  RETAIL_SALE_WITHOUT_GIVECAR\n" +
                " FROM\n" +
                " `lsh车型` AS cx \n" +
                " LEFT  JOIN vw_retailed_quotation AS a ON cx.车型编号=a.`车型编号`\n" +
                " WHERE  a.`许可`=:permit  \n" +
                " AND a.`是否驳回`='否'\n" +
                " AND a.`车辆零售状态`\t='已卖'" +
                " AND a.`开票时间` is not null \n" +
                " AND a.`退票时间` is NULL\n" +
                " AND a.退车日期 is null \n" +
                " and a.`交车日期` is null     " +
                " AND cx.`车型编号`<>0  AND cx.分类 IS NOT NULL  \n" +
                " GROUP BY  cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`;");
        return this.getList(sql.toString(), params);
    }

    /* 截止统计日，有批发日期且无入库日车的车辆台数*/
    @Override
    public List getWholesaleNotIntoStorage(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer(" " +
                " select kc.所属经销商 AS 许可,CONCAT(cx.`分类`,'_',cx.`车型_DE`,'_',cx.`底盘`,'_',cx.`车型`) as uniqueCode, cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`,\n" +
                " count(1) as  RETAIL_SALE_WITHOUT_GIVECAR from  `lsh车型`   AS cx\n" +
                " JOIN `lsh库存表`  AS kc ON  cx.`车型编号`=kc.`车型编号` \n" +
                " where kc.`批发日期` is not NULL\n" +
                " AND kc.`入库日期` is NULL\n" +
                " AND kc.`所属经销商` = :permit  " +
                " AND cx.`车型编号`<>0  AND cx.分类 IS NOT NULL  \n" +
                " GROUP BY  cx.`分类`,cx.`车型_DE`,cx.`底盘`,cx.`车型`;");
        return this.getList(sql.toString(), params);
    }
}
